/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @authors: Pat, Jamie, Kazu, Steve, and Shaoyan
 */
import java.sql.*;
/**
 * 
 * @authors: Pat, Jamie, Kazu, Steve, and Shaoyan
 */
public class Vehicle {

    private DB db;

    /**
     * 
     */
    public Vehicle() {
        this.db = new DB();
    }
    /**
     * 
     * @param vehicleClass
     * @param vehicleID
     * @param depotID
     * @param make
     * @param model
     * @param color
     * @param year
     */
    public void add(int vehicleClass, int vehicleID, int depotID, String make, String model, String color, int year){
        Connection conn = db.connect();
        String query = "INSERT INTO VehicleDB (vehicleclass,depotId,make,model,color,year) "
                +"VALUES ('" + vehicleClass + "','" + depotID + "','" + make + "','" + model + "','" + color + "','" + year
                + "')";
        db.putQuery(conn, query);
        db.disconnect(conn);
    }

    /**
     * 
     * @param vehicleID
     */
    public void removeVehicle(int vehicleID){
        Connection conn = db.connect();
        String query = "DELETE FROM VehicleDB WHERE vehicleId = " + vehicleID;
        db.putQuery(conn, query);
        db.disconnect(conn);
    }

    /**
     * 
     * @param vehicleID
     * @return
     */
    public int getDepotId(int vehicleID){
        int depotID = -1;
        Connection conn = db.connect();
        String query = "SELECT depotId FROM VehicleDB WHERE vehicleId = " + vehicleID;
        ResultSet result = db.getQuery(conn,query);
        db.disconnect(conn);
        try{
            result.next();
            depotID = result.getInt("depotId");
        }catch(Exception e) {
            System.out.println( e.getMessage());
        }
        return depotID;
    }

    /**
     * 
     * @param vehicleID
     * @return
     */
    public int getVehicleClass(int vehicleID){
        int vehicleClass = -1;
        Connection conn = db.connect();
        String query = "SELECT vehicleClass FROM VehicleDB WHERE vehicleId = " + vehicleID;
        ResultSet result = db.getQuery(conn,query);
        db.disconnect(conn);
        try{
            result.next();
            vehicleClass = result.getInt("vehicleClass");
        }catch(Exception e){ }
        return vehicleClass;
    }

    /**
     * 
     * @param vehicleID
     * @param depotID
     */
    public void setDepot(int vehicleID, int depotID){
        Connection conn = db.connect();
        String query = "UPDATE VehicleDB SET depotId = " + depotID
                + " WHERE vehicleId = " + vehicleID;
        db.putQuery(conn, query);
        db.disconnect(conn);
    }
    
    /**
     * 
     * @param depotId
     * @param vehicleClass
     * @param bookingDate
     * @param startTime
     * @param endTime
     * @return
     */
    public int getAvailableVehicle(int depotId, int vehicleClass, Date bookingDate, int startTime, int endTime) {
        int vehicleId = -1;
        Connection conn = db.connect();
        String query = "SELECT vehicleId FROM VehicleDB WHERE vehicleClass="
                +vehicleClass+" AND depotId="+depotId;
        ResultSet result = db.getQuery(conn,query);
        try{
            while (result.next()) {
                int vId = result.getInt("vehicleId");
                String query2 = "SELECT count(*) FROM ScheduleDB WHERE vehicleId="
                        +vId+" AND bookingDate='"+bookingDate+"'"
                        +" AND ((bookingStartTime <="+(startTime-1)
                        +" AND bookingEndTime >="+(startTime-1)
                        +") OR (bookingStartTime <="+(endTime+1)
                        +" AND bookingStartTime>="+(startTime)+"))";
                ResultSet result2 = db.getQuery(conn, query2);
                result2.next();
                int count = result2.getInt("count(*)");
                System.out.println("Vid = " + vId + "   Count = " + count);
                if (count==0) {
                    vehicleId = vId;
                    break;
                }
            }
        }catch(Exception e){
            System.out.println( e.getMessage());
        }
        db.disconnect(conn);
        return vehicleId;
    }
    
    /**
     * 
     * @param vehicleClass
     * @return
     */
    public String getVehicleType(int vehicleClass) {
        String vehicleType = "";
        Connection conn = db.connect();
        String query = "SELECT classType FROM VehicleClassDB WHERE classId="+vehicleClass;
        ResultSet result = db.getQuery(conn,query);
        try{
            if (result.next()) {
                vehicleType = result.getString("classType");
            }
        }catch(Exception e){
            System.out.println( e.getMessage());
        }
        db.disconnect(conn);
        
        return vehicleType;
    }
    /**
     * 
     * @param vehicleClass
     * @return
     */
    public int getVehicleId(String vehicleClass){
        int vehicleId = 0;
        Connection conn = db.connect();
        String query = "SELECT classId FROM VehicleClassDB WHERE classType="+vehicleClass;
        ResultSet result = db.getQuery(conn, query);
        try{
            if (result.next()) {
                vehicleId = result.getInt("classId");
            }
        }catch(Exception e){
            System.out.println( e.getMessage());
        }
        db.disconnect(conn);
        
        return vehicleId;
    }
    
    public String[] getVehicleInfo(int vehicleID){
        String[] info = new String[7];
        Connection conn = db.connect();
        String query = "SELECT * FROM VehicleDB WHERE vehicleId="+vehicleID;
        ResultSet result = db.getQuery(conn, query);
        try{
            if (result.next()) {
                info[0] = result.getString("VehicleClass");
                info[1] = result.getString("DepotId");
                info[2] = result.getString("make");
                info[3] = result.getString("model");
                info[4] = result.getString("colour");
                info[5] = result.getString("year");
                
            }
        }catch(Exception e){
            System.out.println( e.getMessage());
        }
        db.disconnect(conn);
        
       
        return info;
    }

}
